package com.njlime.cms.repository.sys;

import com.njlime.cms.entities.PublishReport;
import com.njlime.core.base.repository.impl.BaseDaoImpl;
import com.njlime.core.helper.PageBean;
import com.njlime.core.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class PublishReportDaoImpl extends BaseDaoImpl<PublishReport>{

    /**
     * 发布统计
     * @param pageBean
     * @param start_time
     * @param end_time
     * @param unit_id
     * @return
     */
	public PageBean publishReportList(PageBean pageBean, String start_time, String end_time, Integer unit_id){
        StringBuffer sb = new StringBuffer();
        List<Object> arrList = new ArrayList<Object>();
		if(!StringUtils.isNotEmpty(start_time)){
			start_time = "2017-01-01 00:00:00";
		}else{
			start_time += " 00:00:00";
		}
		if(!StringUtils.isNotEmpty(end_time)){
			end_time = DateUtil.dateFormat(new Date(),"yyyy-MM-dd 23:59:59");
		}else{
			end_time += " 23:59:59";
		}
        sb.append("select t.unit_id,u.unit_name unit_name,sum(t.count) count,IFNULL(pr.reading,0) reading,create_time ");
		sb.append("from ( ");
		sb.append("select unit_id,count(*) count,max(create_time) create_time from t_information where create_time >= ? and create_time <= ? group by unit_id union all ");
		arrList.add(start_time);
		arrList.add(end_time);
		sb.append("select unit_id,count(*) count,max(create_time) create_time from t_read where create_time >= ? and create_time <= ? group by unit_id union all ");
		arrList.add(start_time);
		arrList.add(end_time);
		sb.append("select unit_id,count(*) count,max(create_time) create_time from t_live where create_time >=? and create_time <= ? group by unit_id union all ");
		arrList.add(start_time);
		arrList.add(end_time);
		sb.append("select unit_id,count(*) count,max(create_time) create_time from t_community where create_time >= ? and create_time <= ? group by unit_id ");
		arrList.add(start_time);
		arrList.add(end_time);
		sb.append(") t ");
		sb.append("LEFT JOIN (select business_unit,sum(reading) reading from t_publish_report t where DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') >= ? and DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') <= ? group by business_unit) pr on pr.business_unit = t.unit_id ");
		arrList.add(start_time);
		arrList.add(end_time);
		sb.append("join t_unit u on u.id = t.unit_id ");
		sb.append("where 1=1 ");
		if(unit_id != null ){
			sb.append("and t.unit_id = ? ");
			arrList.add(unit_id);
		}
		sb.append("group by t.unit_id ");
		int num = getListJdbcSize(sb.toString(),arrList);
		pageBean.setTotalPage(num);
		if (num > 0) {
			sb.append(" order by count desc limit ?,?");
			arrList.add((pageBean.getCurPage() - 1) * pageBean.getPageSize());
			arrList.add(pageBean.getPageSize());
			pageBean.setMapList(getListJdbc(sb.toString(), arrList));
		}
		return pageBean;
	}

    /**
     * 发布统计详情
     * @param start_time
     * @param end_time
     * @param unit_id
     * @return
     */
	public List<Map<String,Object>> publishReportAnalyse(String start_time,String end_time,Integer unit_id){
		StringBuffer sb = new StringBuffer();
		List<Object> arrList = new ArrayList<Object>();
		if(!StringUtils.isNotEmpty(start_time)){
			start_time = "2017-01-01 00:00:00";
		}else{
			start_time += " 00:00:00";
		}
		if(!StringUtils.isNotEmpty(end_time)){
			end_time = DateUtil.dateFormat(new Date(),"yyyy-MM-dd 23:59:59");
		}else{
			end_time += " 23:59:59";
		}
		arrList.add(unit_id);
		arrList.add(start_time);
		arrList.add(end_time);
		int total = (int)findCount("select sum(reading) num from t_publish_report t where t.business_unit = ? and DATE_FORMAT(t.date,'%Y-%m-%d %H:%i:%s') >= ? and DATE_FORMAT(t.date,'%Y-%m-%d %H:%i:%s') <= ?", arrList);
		arrList.clear();
		sb.append("select unit_id,u.unit_name unit_name,sum(reading) reading,ROUND(sum(reading)/? * 100,2) percent from t_publish_report t ");
		arrList.add(total);
		sb.append("join t_unit u on u.id = t.unit_id ");
		sb.append("where business_unit = ? ");
		arrList.add(unit_id);
		sb.append("and DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') >= ? and DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') <= ? ");
		arrList.add(start_time);
		arrList.add(end_time);
		sb.append("group by t.unit_id ");
		sb.append("order by reading desc");
		return getListJdbc(sb.toString(),arrList);
	}

}
